/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.ServiceDao;
import com.tesam.gedeon.entity.Provider;
import com.tesam.gedeon.entity.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author joelpacheco
 */
@Repository
public class ServiceDaoImpl extends SimpleJdbcDaoSupport implements ServiceDao {

    @Autowired
    public ServiceDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int save(Service service) {
        return getJdbcTemplate().update("INSERT INTO service"
                + "("
                + "createdBy,modifiedby,dateCreated,status,deleted,presence,ownerid,code,providerid,name"
                + ")VALUES(?,?,NOW(),?,?,?,?,?,?,?)", service.getCreatedBy(), 0, 1, 0, 1, 1, service.getCode().trim(), service.getProvider().getId(), service.getName().trim());
    }

    @Override
    public int delete(Service service) {
        return getJdbcTemplate().update("UPDATE service SET modifiedby = ?, datemodified = NOW(),deleted = 1 WHERE id = ?", service.getModifiedBy(), service.getId());
    }

    @Override
    public int update(Service service) {
        return getJdbcTemplate().update("UPDATE service SET modifiedby = ?,datemodified = NOW(), code = ?,providerid = ?,name = ? WHERE id = ?",
                service.getModifiedBy(), service.getCode().trim(), service.getProvider().getId(), service.getName(), service.getId());
    }

    @Override
    public Service getById(int serviceId) {
        try {

            RowMapper<Service> rowMapper = new RowMapper<Service>() {
                @Override
                public Service mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    Service service = new Service();

                    service.setId(resultSet.getInt("id"));
                    service.setCreatedBy(resultSet.getInt("createdby"));
                    service.setDateCreated(resultSet.getTimestamp("datecreated"));
                    service.setDateModified(resultSet.getTimestamp("datemodified"));
                    service.setCode(resultSet.getString("code"));
                    service.setName(resultSet.getString("name"));

                    Provider provider = new Provider(resultSet.getString("code"), resultSet.getString("providername"));
                    provider.setId(resultSet.getInt("providerid"));

                    service.setProvider(provider);

                    return service;
                }
            };

            return getSimpleJdbcTemplate().queryForObject("SELECT service.id,service.createdby,service.modifiedby, service.datecreated,service.datemodified,service.status,service.code,"
                    + " service.providerid,service.name,provider.name as providername FROM service INNER JOIN provider"
                    + " ON service.providerid = provider.id AND service.deleted = 0 WHERE service.id = ?", rowMapper, serviceId);
        } catch (Exception e) {
            System.out.println("Exception-Service-getById:" + e.getMessage());
        }
        return null;
    }

    @Override
    public List<Service> getAll() {
        return getSimpleJdbcTemplate().query("SELECT * FROM service WHERE deleted = 0", new BeanPropertyRowMapper<Service>(Service.class));
    }

    @Override
    public List<Service> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        String query = null;
        try {

            query = getLimit("SELECT service.id,service.createdby,service.modifiedby, service.datecreated,service.datemodified,service.status,service.code,"
                    + " service.providerid,service.name,provider.name as providername FROM service INNER JOIN provider"
                    + " ON service.providerid = provider.id AND service.deleted = 0", startLimit, endLimit, dataToSort, directionOfOrder);

            RowMapper<Service> rowMapper = new RowMapper<Service>() {
                @Override
                public Service mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    Service service = new Service();

                    service.setId(resultSet.getInt("id"));
                    service.setCreatedBy(resultSet.getInt("createdby"));
                    service.setDateCreated(resultSet.getTimestamp("datecreated"));
                    service.setDateModified(resultSet.getTimestamp("datemodified"));
                    service.setCode(resultSet.getString("code"));
                    service.setName(resultSet.getString("name"));
                    // System.out.println(resultSet.getInt("providerid"));
                    Provider provider = new Provider(resultSet.getString("code"), resultSet.getString("providername"));

                    service.setProvider(provider);

                    return service;
                }
            };

            return getSimpleJdbcTemplate().query(query, rowMapper);

        } catch (Exception e) {
            System.out.println("getAllByLimit : Exception :" + e.getMessage());
            return null;
        }
    }

    private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }

    @Override
    public List<Service> searchByName(Service service, int satartLimit, int endLimit) {
        String query = null;
        try {
            query = getLimit("SELECT service.id,service.createdby,service.modifiedby, service.datecreated,service.datemodified,service.status,service.code,"
                    + " service.providerid,service.name,provider.name as providername FROM service INNER JOIN provider"
                    + " ON service.providerid = provider.id AND service.deleted = 0 WHERE upper(service.name) LIKE ('%'||?||'%')", satartLimit, endLimit, "service.name", "ASC");

            RowMapper<Service> rowMapper = new RowMapper<Service>() {
                @Override
                public Service mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    Service service = new Service();

                    service.setId(resultSet.getInt("id"));
                    service.setCreatedBy(resultSet.getInt("createdby"));
                    service.setDateCreated(resultSet.getTimestamp("datecreated"));
                    service.setDateModified(resultSet.getTimestamp("datemodified"));
                    service.setCode(resultSet.getString("code"));
                    service.setName(resultSet.getString("name"));
                    // System.out.println(resultSet.getInt("providerid"));
                    Provider provider = new Provider(resultSet.getString("code"), resultSet.getString("providername"));

                    service.setProvider(provider);

                    return service;
                }
            };

            return getSimpleJdbcTemplate().query(query, rowMapper, service.getName());
        } catch (Exception e) {
            System.out.println("searchByName:Exception:" + e.getMessage());
            return null;
        }
    }

    @Override
    public int getCountSearchByName(Service service) {
        return getSimpleJdbcTemplate().queryForInt("SELECT count(*) FROM service WHERE deleted = 0 AND upper(name) LIKE ('%' || ? || '%')", service.getName());
    }

    @Override
    public int getCountTotal() {
        return getSimpleJdbcTemplate().queryForInt("SELECT count(*) FROM service WHERE deleted = 0");
    }
}
